﻿namespace AnyOffice.DAL
{
    using AnyOffice.Components;
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class BbsCommentDao
    {
        public bool Delete(SqlConnection conn, int commendId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DECLARE @ParentId\tint ");
            builder.Append("SELECT ");
            builder.Append("\t@ParentId = [ParentId] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Bbs_Comment] ");
            builder.Append("WHERE ");
            builder.Append("\t[CommentId] = @CommentId ");
            builder.Append("IF @ParentId = 0 ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tUPDATE ");
            builder.Append("\t\t\t[dbo].[Attachment] ");
            builder.Append("\t\tSET ");
            builder.Append("\t\t\t[DeleteFlag] = 1 ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[AttachmentId] IN ");
            builder.Append("\t\t\t( ");
            builder.Append("\t\t\t\tSELECT ");
            builder.Append("\t\t\t\t\t[AttachmentId] ");
            builder.Append("\t\t\t\tFROM ");
            builder.Append("\t\t\t\t\t[dbo].[Bbs_Comment] ");
            builder.Append("\t\t\t\tWHERE ");
            builder.Append("\t\t\t\t\tISNULL([AttachmentId],'') <> '' ");
            builder.Append("\t\t\t\t\tAND [CommentId] = @CommentId ");
            builder.Append("\t\t\t\t\tOR [ParentId] = @CommentId ");
            builder.Append("\t\t\t) ");
            builder.Append("\t\tDELETE FROM ");
            builder.Append("\t\t\t[dbo].[Bbs_Comment] ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[CommentId] = @CommentId ");
            builder.Append("\t\t\tOR [ParentId] = @CommentId ");
            builder.Append("\tEND ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tUPDATE ");
            builder.Append("\t\t\t[dbo].[Attachment] ");
            builder.Append("\t\tSET ");
            builder.Append("\t\t\t[DeleteFlag] = 1 ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[AttachmentId] IN ");
            builder.Append("\t\t\t( ");
            builder.Append("\t\t\t\tSELECT ");
            builder.Append("\t\t\t\t\t[AttachmentId] ");
            builder.Append("\t\t\t\tFROM ");
            builder.Append("\t\t\t\t\t[dbo].[Bbs_Comment] ");
            builder.Append("\t\t\t\tWHERE ");
            builder.Append("\t\t\t\t\tISNULL([AttachmentId],'') <> '' ");
            builder.Append("\t\t\t\t\tAND [CommentId] = @CommentId\t\t\t\t\t ");
            builder.Append("\t\t\t) ");
            builder.Append("\t\tDELETE FROM ");
            builder.Append("\t\t\t[dbo].[Bbs_Comment] ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[CommentId] = @CommentId\t\t ");
            builder.Append("\t\tUPDATE ");
            builder.Append("\t\t\t[dbo].[Bbs_Comment] ");
            builder.Append("\t\tSET ");
            builder.Append("\t\t\t[ReplyCount] = [ReplyCount] - 1 ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[CommentId] = @ParentId\t ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@CommentId", SqlDbType.Int) };
            commandParameters[0].Value = commendId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, BbsCommentData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@ParentId <> 0) ");
            builder.Append("\tUPDATE ");
            builder.Append("\t\t[dbo].[Bbs_Comment] ");
            builder.Append("\tSET ");
            builder.Append("\t\t[ReplyCount] = [ReplyCount] + 1, ");
            builder.Append("\t\t[LastReplyTime] = GETDATE() ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[CommentId] = @ParentId ");
            builder.Append("INSERT INTO [dbo].[Bbs_Comment] ");
            builder.Append("( ");
            builder.Append("\t[BoardId], ");
            builder.Append("\t[ParentId], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[AuthorName], ");
            builder.Append("\t[Subject], ");
            builder.Append("\t[Content], ");
            builder.Append("\t[AttachmentId], ");
            builder.Append("\t[SubmitTime], ");
            builder.Append("\t[ReplyCount], ");
            builder.Append("\t[ReadCount], ");
            builder.Append("\t[LastReplyTime] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@BoardId, ");
            builder.Append("\t@ParentId, ");
            builder.Append("\t@UserId, ");
            builder.Append("\t@AuthorName, ");
            builder.Append("\t@Subject, ");
            builder.Append("\t@Content, ");
            builder.Append("\t@AttachmentId, ");
            builder.Append("\t@SubmitTime, ");
            builder.Append("\t@ReplyCount, ");
            builder.Append("\t@ReadCount, ");
            builder.Append("\t@LastReplyTime ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[11];
            commandParameters[0] = new SqlParameter("@BoardId", SqlDbType.Int);
            commandParameters[0].Value = data.BoardId;
            commandParameters[1] = new SqlParameter("@ParentId", SqlDbType.Int);
            commandParameters[1].Value = data.ParentId;
            commandParameters[2] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[2].Value = data.UserId;
            commandParameters[3] = new SqlParameter("@AuthorName", SqlDbType.NVarChar, 50);
            if (data.AuthorNameIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.AuthorName;
            }
            commandParameters[4] = new SqlParameter("@Subject", SqlDbType.NVarChar, 200);
            commandParameters[4].Value = data.Subject;
            commandParameters[5] = new SqlParameter("@Content", SqlDbType.NText);
            if (data.ContentIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.Content;
            }
            commandParameters[6] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.AttachmentId;
            }
            commandParameters[7] = new SqlParameter("@SubmitTime", SqlDbType.DateTime);
            commandParameters[7].Value = data.SubmitTime;
            commandParameters[8] = new SqlParameter("@ReplyCount", SqlDbType.Int);
            commandParameters[8].Value = data.ReplyCount;
            commandParameters[9] = new SqlParameter("@ReadCount", SqlDbType.Int);
            commandParameters[9].Value = data.ReadCount;
            commandParameters[10] = new SqlParameter("@LastReplyTime", SqlDbType.DateTime);
            commandParameters[10].Value = data.LastReplyTime;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable ReadByCommentId(SqlConnection conn, int commendId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Bbs_Comment] ");
            builder.Append("SET ");
            builder.Append("\t[ReadCount] = [ReadCount]+1 ");
            builder.Append("WHERE ");
            builder.Append("\t[CommentId] = @CommentId ");
            builder.Append("\t ");
            builder.Append("SELECT ");
            builder.Append("\t[Bbs_Comment].*, ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\t[Users].[Avatar] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Bbs_Comment], ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("WHERE ");
            builder.Append("\t( ");
            builder.Append("\t\t[Bbs_Comment].[CommentId] = @CommentId ");
            builder.Append("\t\tOR [Bbs_Comment].[ParentId] = @CommentId ");
            builder.Append("\t) ");
            builder.Append("\tAND [Bbs_Comment].[UserId] = [Users].[UserId] ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Bbs_Comment].[CommentId] ASC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@CommentId", SqlDbType.Int) };
            commandParameters[0].Value = commendId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Bbs_Comment";
            return set.Tables[0];
        }

        public DataTable SelectByCommentId(SqlConnection conn, int commendId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[CommentId], ");
            builder.Append("\t[BoardId], ");
            builder.Append("\t[ParentId], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[AuthorName], ");
            builder.Append("\t[Subject], ");
            builder.Append("\t[Content], ");
            builder.Append("\t[AttachmentId], ");
            builder.Append("\t[SubmitTime], ");
            builder.Append("\t[ReplyCount], ");
            builder.Append("\t[ReadCount], ");
            builder.Append("\t[LastReplyTime] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Bbs_Comment] ");
            builder.Append("WHERE ");
            builder.Append("\t[CommentId] = @CommentId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@CommentId", SqlDbType.Int) };
            commandParameters[0].Value = commendId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Bbs_Comment";
            return set.Tables[0];
        }

        public DataTable SelectMyTable(SqlConnection conn, string userId, int deptId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT\tTOP " + new ConfigManager().BbsNum.ToString());
            builder.Append("\t[Bbs_Comment].*, ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\t[Department].[DeptName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Bbs_Board], ");
            builder.Append("\t[dbo].[Bbs_Comment], ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Department]\t ");
            builder.Append("WHERE ");
            builder.Append("\t( ");
            builder.Append("\t\tISNULL(CAST([Bbs_Board].[DeptId] AS nvarchar(2000)),'') = 'ALL_DEPT' ");
            builder.Append("\t\tOR CHARINDEX(',' + CAST(@DeptId AS nvarchar(20)) + ',',',' + ISNULL(CAST([Bbs_Board].[DeptId] AS nvarchar(2000)),'') + ',') > 0 ");
            builder.Append("\t\tAND CHARINDEX(',' + @UserId + ',',',' + ISNULL(CAST([Bbs_Board].[Owner] AS nvarchar(2000)),'') + ',') > 0 ");
            builder.Append("\t) ");
            builder.Append("\tAND [Bbs_Board].[BoardId] = [Bbs_Comment].[BoardId] ");
            builder.Append("\tAND [Bbs_Comment].[UserId] = [Users].[UserId] ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append(" ORDER BY ");
            builder.Append("\t[Bbs_Comment].[LastReplyTime] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = userId;
            commandParameters[1] = new SqlParameter("@DeptId", SqlDbType.Int);
            commandParameters[1].Value = deptId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Bbs_Comment";
            return set.Tables[0];
        }

        public DataTable SelectPaged(SqlConnection conn, int pageSize, int pageIndex, bool docount, int boardId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT COUNT([CommentId]) FROM [dbo].[Bbs_Comment] WHERE [BoardId] = @BoardId AND [ParentId]=0 ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @IndexTable TABLE(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\t ");
            builder.Append("\t\tINSERT INTO @IndexTable(PK) SELECT [CommentId] FROM [dbo].[Bbs_Comment] WHERE [BoardId] = @BoardId AND [ParentId]=0 ORDER BY [LastReplyTime] DESC ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[Bbs_Comment].*, ");
            builder.Append("\t\t\t[Users].[UserName], ");
            builder.Append("\t\t\t[Users].[Avatar], ");
            builder.Append("\t\t\t@pageindex AS pageindex ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Bbs_Comment], ");
            builder.Append("\t\t\t[dbo].[Users], ");
            builder.Append("\t\t\t@IndexTable t\t ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[Bbs_Comment].[UserId] = [Users].[UserId] ");
            builder.Append("\t\t\tAND [Bbs_Comment].[CommentId] = t.PK ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound  ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound ");
            builder.Append("\t\t ORDER BY ");
            builder.Append("\t\t\t[LastReplyTime] DESC ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pageSize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageIndex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            commandParameters[3] = new SqlParameter("@BoardId", SqlDbType.Int);
            commandParameters[3].Value = boardId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Bbs_Comment";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, BbsCommentData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Bbs_Comment] SET ");
            builder.Append("\t[UserId] = @UserId, ");
            builder.Append("\t[AuthorName] = @AuthorName, ");
            builder.Append("\t[Subject] = @Subject, ");
            builder.Append("\t[Content] = @Content, ");
            builder.Append("\t[AttachmentId] = @AttachmentId ");
            builder.Append("WHERE ");
            builder.Append("\t[CommentId] = @CommentId ");
            SqlParameter[] commandParameters = new SqlParameter[12];
            commandParameters[0] = new SqlParameter("@CommentId", SqlDbType.Int);
            commandParameters[0].Value = data.CommentId;
            commandParameters[1] = new SqlParameter("@BoardId", SqlDbType.Int);
            commandParameters[1].Value = data.BoardId;
            commandParameters[2] = new SqlParameter("@ParentId", SqlDbType.Int);
            commandParameters[2].Value = data.ParentId;
            commandParameters[3] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[3].Value = data.UserId;
            commandParameters[4] = new SqlParameter("@AuthorName", SqlDbType.NVarChar, 50);
            if (data.AuthorNameIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.AuthorName;
            }
            commandParameters[5] = new SqlParameter("@Subject", SqlDbType.NVarChar, 200);
            commandParameters[5].Value = data.Subject;
            commandParameters[6] = new SqlParameter("@Content", SqlDbType.NText);
            if (data.ContentIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.Content;
            }
            commandParameters[7] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.AttachmentId;
            }
            commandParameters[8] = new SqlParameter("@SubmitTime", SqlDbType.DateTime);
            commandParameters[8].Value = data.SubmitTime;
            commandParameters[9] = new SqlParameter("@ReplyCount", SqlDbType.Int);
            commandParameters[9].Value = data.ReplyCount;
            commandParameters[10] = new SqlParameter("@ReadCount", SqlDbType.Int);
            commandParameters[10].Value = data.ReadCount;
            commandParameters[11] = new SqlParameter("@LastReplyTime", SqlDbType.DateTime);
            commandParameters[11].Value = data.LastReplyTime;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

